﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using WF_DataAccess;
using WF_Business;

namespace Business.Admin
{
    /// <summary>
    /// 部门管理操作类
    /// 修改人：YZG
    /// 时间：2010-03-01
    /// </summary>
    public class DepartHandle
    {
        
        /// <summary>
        /// 获取部门信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetDepartment()
        {
            string sqlstring = "select departid,depart_name from st_department";
            DataTable dtlist;
            SysParams.OAConnection().RunSql(sqlstring, out dtlist);
            return dtlist;
        }

        /// <summary>
        /// 获得所有一级部门列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetDepartmentWithZero()
        {
            String strSql = "select * from st_department where (parent_id='0'  or parent_id is null) order by order_id";
            DataTable dt;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 获取非一级部门信息列表
        /// </summary>
        /// <returns></returns>
        /// <!--addby zhongjian 20091201-->
        public DataTable GetSecondDepart()
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select * from st_department t where departid<>35 
                 start with t.parent_id='0'
                 connect by prior departid=parent_id");
            DataTable dt;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 查询部门信息
        /// </summary>
        /// <param name="departId"></param>
        /// <returns></returns>
        public DataTable SearchDepartment(string departId)
        {
            string sqlstr = "select parent_id from st_department where departid='" + departId + "'";
            DataTable dt;
            SysParams.OAConnection().RunSql(sqlstr, out dt);
            return dt;
        }
        /// <summary>
        /// 获取最大部门号
        /// </summary>
        /// <returns></returns>
        public string GetMaxDepart()
        {
            string sql = "SELECT MAX(DepartId)+1 FROM st_department ";
            string departId = SysParams.OAConnection().GetValue(sql);
            return departId;
        }
        /// <summary>
        /// 新增部门
        /// </summary>
        public void AddDepartment(string bmmc, string sjbmlist, string orderId)
        {
            string sql = "";
            string departId = GetMaxDepart();

            sql = "INSERT INTO st_department(departid,depart_name, parent_id, order_id) VALUES('" + departId
                + "','" + bmmc + "','" + sjbmlist + "','" + orderId + "')";
            SysParams.OAConnection().RunSql(sql);
        }
        /// <summary>
        /// 修改部门
        /// </summary>
        /// <param name="bmmc"></param>
        /// <param name="sjbm"></param>
        /// <param name="orderId"></param>
        /// <param name="departId"></param>
        public void UpdateDepartment(string bmmc, string sjbm, string orderId, string departId)
        {
            IDataAccess ida = SysParams.OAConnection(true);
            string sql = "select order_id from st_department where departid='" + departId + "'";
            string OldOrderId = SysParams.OAConnection().GetValue(sql);
            try
            {
                sql = "update st_department set depart_name='" + bmmc + "',parent_id='" + sjbm + "',order_id='"
                    + orderId + "' where departid ='" + departId + "'";
                ida.RunSql(sql);
                if (OldOrderId != orderId)  //如果产生了新orderid,则需更新其下级部门的orderid
                {
                    int pos = OldOrderId.Length + 1;
                    sql = "update st_department set order_id ='" + orderId + "'||substr(order_id," + pos.ToString() + ")"
                    + " where order_id like '" + OldOrderId + "%'";
                    ida.RunSql(sql);
                }
                ida.Close(true);
            }
            catch
            {
                ida.Close(false);
                throw;
            }
        }
        /// <summary>
        /// 删除部门
        /// </summary>
        /// <param name="bmid"></param>
        /// <returns></returns>
        public int DeleteDepartment(string bmid)
        {
            string del_userdepart = "delete from st_user_department where order_id ='" + bmid + "'";
            int j = SysParams.OAConnection().RunSql(del_userdepart);

            string strSql = "delete from st_department where departid='" + bmid + "'";
            int i = SysParams.OAConnection().RunSql(strSql);
            return i;
        }
        /// <summary>
        /// 获取部门编号
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetDepartId(string id)
        {
            string sql = "SELECT parent_id FROM st_department WHERE DepartId='" + id + "'";
            return SysParams.OAConnection().GetValue(sql);
        }
        /// <summary>
        /// 获取最大排序号
        /// </summary>
        /// <returns></returns>
        public string GetMaxOrderId()
        {
            string sql = "SELECT Max(to_number(ORDER_ID))+1 FROM st_department";
            string orderId = SysParams.OAConnection().GetValue(sql);

            //初始牌序号为1
            if (string.IsNullOrEmpty(orderId))
            {
                orderId = "1";
            }

            return orderId;
        }

        /// <summary>
        /// 获取取部门名
        /// </summary>
        /// <returns></returns>
        public DataTable GetDepartmentName()
        {
            string sqlstring = "select depart_name from st_department where parent_id='0'";
            DataTable dt;
            SysParams.OAConnection().RunSql(sqlstring, out dt);
            return dt;
        }
        /// <summary>
        /// 获取部门信息
        /// </summary>
        /// <param name="danwei"></param>
        /// <returns></returns>
        public DataTable GetDepartment(string danwei)
        {
            string sqlstrings = "select depart_name from st_department where parent_id in(select departid from st_department where depart_name like '" + danwei + "')";
            DataTable dtbumen;
            SysParams.OAConnection().RunSql(sqlstrings, out dtbumen);
            return dtbumen;
        }

        /// <summary>
        /// 获得部门下的科室(包含与部门名称重复的科室)
        /// </summary>
        /// <param name="orderId">Order_Id</param>
        /// <returns></returns>
        public DataTable GetDepartmentByParentId(string parent_id)
        {
            if (String.IsNullOrEmpty(parent_id))
            {
                parent_id = "0";
            }
            DataTable rs;
            string sql = "select * from st_department where PARENT_ID ='" + parent_id + "' order by order_id";
            SysParams.OAConnection().RunSql(sql, out rs);

            if (rs.Rows.Count == 0)
            {
                sql = "select * from st_department where DEPARTID ='" + parent_id + "' order by order_id";
                SysParams.OAConnection().RunSql(sql, out rs);
            }
            return rs;
        }

        /// <summary>
        /// 查询部门内用户
        /// </summary>
        /// <returns></returns>
        public static DataTable GeDepartmentByDepartId(string depId)
        {
            //用户信息查询
            string sql = "select b.userid,b.user_name from st_user_department a,st_user b Where a.userid=b.userid And a.order_id='" + depId + "'";

            //string sql = string.Format("select b.userid,b.user_name from st_user b Where b.deptid='{0}'",depId);//Departid
            DataTable dt;
            SysParams.OAConnection().RunSql(sql, out dt);
            return dt;
        }

        /// <summary>
        /// 获取指定部门的所欲用户
        /// </summary>
        /// <param name="strDeptId"></param>
        /// <returns></returns>
        public DataTable GetDepartById(string strDeptId)
        {
            //string strSql = string.Format("select n.userid,n.login_name,n.User_Name from st_user n where n.deptid='{0}'",strDeptId);//Departid

            string strSql = string.Format("select b.userid,b.login_name,b.user_name from st_user_department a,st_user b Where a.userid=b.userid And a.order_id='{0}'", strDeptId);
            DataTable dt;

            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 获得指定部门的所有用户
        /// </summary>
        /// <param name="strDeptId">部门编号</param>
        /// <returns></returns>
        public DataTable GetDepartmentUser(string strDeptId)
        {
            String strSql = "select n.userid,n.login_name,n.User_Name from st_user_department m,st_user n";
            strSql += " where m.userid=n.userid";
            strSql += " and m.order_id ='" + strDeptId + "'";
            strSql += " and n.invalid ='1'";
            strSql += " order by n.orderbyid";
            DataTable dt;

            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 获取指定部门的所欲用户
        /// </summary>
        /// <param name="strDeptId"></param>
        /// <returns></returns>
        public DataTable GetDepartUser(string strDeptId)
        {
            //string strSql = string.Format("select n.userid,n.login_name,n.User_Name from st_user n where n.deptid='{0}'",strDeptId);//Departid

            string strSql = string.Format("select b.userid,b.login_name,b.user_name from st_user_department a,st_user b Where a.userid=b.userid And a.order_id='{0}'", strDeptId);
            DataTable dt;

            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 重置部门
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="selecteduser"></param>
        /// <param name="sortID"></param>
        /// <param name="bmid"></param>
        public static void ResetDepartment(string orderId, string[] selecteduser, string[] sortID, string bmid)
        {
            IDataAccess dataAccess = SysParams.OAConnection(true);
            //获取form的值

            if (orderId != "")
            {
                string del_user = "delete from st_user_department where order_id = '" + bmid + "'";
                dataAccess.RunSql(del_user);
                if (selecteduser.Length > 0)
                {
                    int sortPos = 0;
                    foreach (string strUserId in selecteduser)
                    {
                        if (strUserId != "")
                        {
                            string insert_user = string.Format("insert into st_user_department (userid, order_id) values ('{0}','{1}')",
                                strUserId, bmid);
                            dataAccess.RunSql(insert_user);
                            string update_sortID = "update st_user set orderbyid='" + sortID[sortPos] + "',OPERATION_FLAG=3  where userid='" + strUserId + "'";
                            dataAccess.RunSql(update_sortID);
                        }
                        sortPos++;
                    }

                }
                dataAccess.Close(true);
            }

        }

        /// <summary>
        /// 根据用户ID查询用户所在部门ID
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable dtUserDepart(string userid)
        {
            string strSql = "select t.order_id  from st_user_department t where t.userid = '" + userid + "'";
            DataTable dt;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        

        /// <summary>
        /// 获取部门关系树
        /// </summary>
        /// <returns></returns>
        public DataTable GetDepartmentTree()
        {
            String strSql = @"select * from st_department t start with (t.parent_id='0' or t.parent_id is null) 
                 connect by prior departid=parent_id";
            DataTable dt;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 根据流程名和岗位名获取组号
        /// </summary>
        /// <param name="strWorkName">流程名</param>
        /// <param name="strStepName">岗位名</param>
        /// <returns></returns>
        public static String GetGroupIdOfStep(String strWorkName, String strStepName)
        {
            Business.FlowOperation.ClsUserWorkFlow clsWorkFlow = new Business.FlowOperation.ClsUserWorkFlow();
            string strWid = clsWorkFlow.GetFlowIdByName(strWorkName);

            String sql = string.Format("select GID from st_group_in_step where wid='{0}' and stpname='{1}'", strWid, strStepName);
            string strGroupId = SysParams.OAConnection().GetValue(sql);

            return strGroupId;
        }

       

        /// <summary>
        /// 根据部门id获取部门名称
        /// </summary>
        /// <param name="strOrderId">部门id</param>
        /// <returns></returns>
        /// <!--
        /// 创建人  : LinJian
        /// 创建时间: 2007年5月29日
        /// -->
        public string GeDepartmentById(string strOrderId)
        {
            string sql = "select DEPART_NAME from st_department where DEPARTID='" + strOrderId + "' order by order_id";
            return SysParams.OAConnection().GetValue(sql);
        }

        /// <summary>
        /// 获得部门下的科室(不包含与部门名称重复的科室)
        /// </summary>
        /// <param name="orderId">部门id</param>
        /// <returns></returns>
        /// <!--
        /// 创建人  : LinJian
        /// 创建时间: 2007年5月29日
        /// -->
        public DataTable GetChildDepartment(string parent_id)
        {

            if (String.IsNullOrEmpty(parent_id))
            {
                parent_id = "0";
            }
            DataTable rs;
            string sql = "select * from st_department where PARENT_ID ='" + parent_id + "' order by order_id";
            SysParams.OAConnection().RunSql(sql, out rs);

            return rs;
        }

        /// <summary>
        /// 根据部门Id得到部门或部门下面的科室名称
        /// </summary>
        /// <param name="strDepartId">部门Id</param>
        /// <returns></returns>
        /// <!--
        /// 创建人  : LinJian
        /// 创建时间: 2007年5月29日
        /// -->
        public Dictionary<string, string> GetDeptInfo(String strDepartId)
        {
            String sql = "select * from st_department where DEPARTID ='" + strDepartId + "' order by order_id";

            DataTable dtOut;

            Dictionary<string, string> dicReturn = new Dictionary<string, string>();
            SysParams.OAConnection().RunSql(sql, out dtOut);

            if (dtOut.Rows.Count > 0)
            {
                dicReturn.Add("DEPART_NAME", dtOut.Rows[0]["DEPART_NAME"].ToString());
                dicReturn.Add("ORDER_ID", dtOut.Rows[0]["ORDER_ID"].ToString());
                dicReturn.Add("DEPARTID", dtOut.Rows[0]["DEPARTID"].ToString());
            }

            return dicReturn;
        }

        /// <summary>
        /// 获取所有部门信息
        /// </summary>
        /// <returns></returns>
        /// <!--
        /// 创建人  : LinJian
        /// 创建时间: 2007年5月29日
        /// -->
        public DataTable GetAllDeptInfo()
        {
            String sql = "select * from st_department order by order_id";

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            return dtOut;
        }

        /// <summary>
        /// 向上或向下移动部门
        /// </summary>
        /// <param name="strDepId">部门主键</param>
        /// <param name="strWay">移动方向</param>
        public void MoveDepart(string strDepId, string strWay)
        {
            string strSql = "select * from st_department where parent_id=(select parent_id from st_department "
            + "where departid='" + strDepId + "') order by order_id ";
            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            int pos = 0;
            for (int i = 0; i < dtTemp.Rows.Count; i++)
            {
                if (strDepId == dtTemp.Rows[i]["departid"].ToString())
                {
                    pos = i;
                    break;
                }
            }
            string ThisId = dtTemp.Rows[pos]["departid"].ToString(); //要移动部门主键
            string ThisOrderId = dtTemp.Rows[pos]["order_id"].ToString(); //要移动部门order_id

            IDataAccess ida = SysParams.OAConnection(true);
            try
            {
                if (strWay.ToLower() == "up" && pos > 0)  //上移
                {
                    string PreId = dtTemp.Rows[pos - 1]["departid"].ToString(); //上一条记录主键
                    string PreOrderId = dtTemp.Rows[pos - 1]["order_id"].ToString(); //上一条记录order_id
                    strSql = "update st_department set order_id='" + PreOrderId + "' where departid='" + ThisId + "'";
                    ida.RunSql(strSql);
                    strSql = "update st_department set order_id='" + ThisOrderId + "' where departid='" + PreId + "'";
                    ida.RunSql(strSql);
                    //更新下级部门
                    strSql = "update st_department set order_id ='" + PreOrderId + "'||substr(order_id," + (PreOrderId.Length + 1) + ") "
                    + "where parent_id='" + ThisId + "'";
                    ida.RunSql(strSql);
                    strSql = "update st_department set order_id ='" + ThisOrderId + "'||substr(order_id," + (ThisOrderId.Length + 1) + ") "
                    + "where parent_id='" + PreId + "'";
                    ida.RunSql(strSql);
                }
                else if (strWay.ToLower() == "down" && pos < dtTemp.Rows.Count - 1)  //下移
                {
                    string NextId = dtTemp.Rows[pos + 1]["departid"].ToString(); //下一条记录主键
                    string NextOrderId = dtTemp.Rows[pos + 1]["order_id"].ToString(); //上一条记录order_id

                    strSql = "update st_department set order_id='" + NextOrderId + "' where departid='" + ThisId + "'";
                    ida.RunSql(strSql);
                    strSql = "update st_department set order_id='" + ThisOrderId + "' where departid='" + NextId + "'";
                    ida.RunSql(strSql);
                    //更新下级部门
                    strSql = "update st_department set order_id ='" + NextOrderId + "'||substr(order_id," + (NextOrderId.Length + 1) + ") "
                    + "where parent_id='" + ThisId + "'";
                    ida.RunSql(strSql);
                    strSql = "update st_department set order_id ='" + ThisOrderId + "'||substr(order_id," + (ThisOrderId.Length + 1) + ") "
                    + "where parent_id='" + NextId + "'";
                    ida.RunSql(strSql);
                }
                ida.Close(true);
            }
            catch
            {
                ida.Close(false);
                throw;
            }
        }

        /// <summary>
        /// 获取有操作权限的部门
        /// </summary>
        /// <param name="strDepName">所在部门名</param>
        /// <returns></returns>
        public DataTable GetHasRightDep(string strDepName)
        {
            string strSql = "select * from st_department where parent_id=(select parent_id from st_department "
            + "where depart_name='" + strDepName + "') order by order_id ";
            DataTable dt;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 根据userid获取所属部门列表
        /// </summary>
        /// <param name="strUserId"></param>
        /// <returns></returns>
        public DataTable GetDepListByUserid(string strUserId)
        {
            String strSql = string.Format(@"select * from st_department where departid in 
                (select order_id from st_user_department where userid='{0}')", strUserId);
            DataTable dt;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 根据userid获取所属部门列表
        /// </summary>
        /// <param name="strUserId"></param>
        /// <returns></returns>
        public DataTable GetSubDepList(string strUserId)
        {
            String strSql = "select * from st_user_department where userid='" + strUserId + "'";
            DataTable dt;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 获取上级部门id,如果为0则返回本部门id
        /// </summary>
        /// <param name="strDepId"></param>
        /// <returns></returns>
        public string GetParentDepId(string strDepId)
        {
            string strSql = "select parent_id from st_department where departid='" + strDepId + "'";
            string strResult = SysParams.OAConnection().GetValue(strSql);
            if (strResult == "0")
            {
                return strDepId;
            }
            return strResult;
        }

        #region 获取人员列表
        /// <summary>
        /// 获取人员列表
        /// </summary>
        /// <param name="deparementID"></param>
        /// <returns></returns>
        public DataTable GetUserList(string deparementID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select t.login_name,t.user_name from st_user t where t.UserId in  (select UserId from st_user_department where order_id ='{0}') order by t.orderbyid", deparementID);

            //strSql = string.Format(@"select t.login_name,t.user_name from st_user t where t.deptid ='{0}' order by t.orderbyid", deparementID);


            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);

            DataTable dtReturn = dtTemp.Clone();

            DataColumn[] dcArray = new DataColumn[1];
            dcArray[0] = dtReturn.Columns["login_name"];
            dtReturn.PrimaryKey = dcArray;
            foreach (DataRow drTemp in dtTemp.Rows)
            {
                object objLoginName = drTemp["login_name"];
                if (!dtReturn.Rows.Contains(objLoginName))
                {
                    dtReturn.Rows.Add(drTemp.ItemArray);
                }
            }
            return dtReturn;
        }
        #endregion
    }
}
